﻿using MySql.Data.MySqlClient;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Xml;

namespace sqlmanage.common
{
    public class dbomysql : dbo
    {
        public override void xmlsave(System.Collections.Hashtable hashtable)
        {
            string server = hashtable["server"].ToString();
            string user = hashtable["user"].ToString();
            string password = hashtable["password"].ToString();
            string type = hashtable["type"].ToString();

            string strPath = Application.StartupPath + "\\Config" + "\\sql.xml";
            XmlDocument doc = new XmlDocument();
            doc.Load(strPath);
            var rootnode = doc.SelectSingleNode("root");
            XmlNodeList nodeList = doc.SelectSingleNode("root").ChildNodes;

            XmlElement mysql = doc.CreateElement("mysql");
            mysql.SetAttribute("server", server);
            mysql.SetAttribute("user", user);
            mysql.SetAttribute("password", password);
            rootnode.AppendChild(mysql);

            doc.Save(strPath);
        }

        public override DataTable ExcuteSql(string strSql)
        {
            MySqlDataReader thisReader = MysqlHelper.GetReader(strSql);

            DataTable dt = new DataTable();

            for (int i = 0; i < thisReader.FieldCount; i++)
            {
                dt.Columns.Add(thisReader.GetName(i));
            }

            while (thisReader.Read())
            {
                DataRow dr1 = dt.NewRow();
                for (int i = 0; i < thisReader.FieldCount; i++)
                {
                    dr1[i] = thisReader[i].ToString();
                }

                dt.Rows.Add(dr1);
            }

            dt = MysqlHelper.DealTable_Str(dt);

            thisReader.Close();
            return dt;
        }

        public override string xmlload()
        {
            return base.xmlload();
        }

        public override StringBuilder Create(string database, string table)
        {
            //string strSql = "select * FROM " + database + "." + table + "";
            //DataTable dt = MysqlHelper.GetDataTable(strSql);

            string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
            DataTable dataTable = MysqlHelper.GetDataTable(strSql);

            string strFields = "";
            string strValues = "";

            foreach (DataRow dataRow in dataTable.Rows)
            {
                string field = dataRow["field"].ToString();
                string type = dataRow["type"].ToString();
                string nullvalue = dataRow["null"].ToString();
                string key = dataRow["key"].ToString();
                string extra = dataRow["extra"].ToString();
                string comment = dataRow["comment"].ToString();

                if (extra.IndexOf("auto_increment") != -1)
                {
                    continue;
                }

                strFields += "`" + field + "`,";
                strValues += "'1',";
            }

            //foreach (DataColumn dc in dt.Columns)
            //{
            //    strFields += "`" + dc.ColumnName + "`,";
            //    strValues += "'1',";

            //}
            StringBuilder stringBuilder = new StringBuilder();
            if (strValues.Trim() != "")
            {
                strFields = strFields.Substring(0, strFields.Length - 1);
                strValues = strValues.Substring(0, strValues.Length - 1);

                stringBuilder.Append("insert into `" + database + "`.`" + table + "`(" + strFields + ") values(" + strValues + ")\n");
            }
            return stringBuilder;
        }

        public override void connect_database(System.Collections.Hashtable hashtable)
        {
            string server = hashtable["server"].ToString();
            string user = hashtable["user"].ToString();
            string password = hashtable["password"].ToString();
            TreeView treeView = hashtable["treeView"] as TreeView;
            DataGridView dataGridView = hashtable["dataGridView"] as DataGridView;
            //string type = hashtable["type"].ToString();

            string strCon = "server=" + server + ";user id=" + user + "; pooling=false;charset=gb2312; password=" +
                password + "; Allow Zero Datetime=True";
            MysqlHelper.strConnString = strCon;
            string strSql = "SHOW DATABASES";

            MySql.Data.MySqlClient.MySqlDataReader thisReader = MysqlHelper.GetReader(strSql);

            DataTable dt1 = new DataTable();
            dt1.Columns.Add("数据库");

            while (thisReader.Read())
            {
                DataRow dr1 = dt1.NewRow();
                dr1[0] = thisReader[0].ToString();
                dt1.Rows.Add(dr1);

                TreeNode tn = new TreeNode();
                tn.Text = thisReader[0].ToString();
                tn.Tag = "database";
                treeView.Nodes[0].Nodes.Add(tn);
            }
            thisReader.Close();
            dataGridView.DataSource = dt1;
        }
    }
}